﻿using Dapper;
using SetSailModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SetSailDal
{
    public class YHGLDal
    {
        /// <summary>
        /// id获取实体
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static SetSailModel.QH_YHXX GetModel(string ID)
        {
            string sql = string.Format($@"SELECT * FROM [dbo].[QH_YHGL] with (nolock) where id='{ID}'");
            using (DBHelper help = new DBHelper())
            {
                var ls = help.DbContext.Query<QH_YHXX>(sql) as List<QH_YHXX>;
                if (ls.Count > 0)
                {
                    return ls[0];
                }
                else
                {
                    return new QH_YHXX();
                }
            }
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <returns></returns>
        public static ResultModel<List<SetSailModel.QH_YHXX>> GetPageList(int pageIndex, int pageSize, string where, string sort = "id")
        {
            ResultModel<List<QH_YHXX>> result = new ResultModel<List<QH_YHXX>>();
            try
            {
                int skip = 1;//从第几条开始
                int take = pageIndex * pageSize;//到第几条结束
                if (pageIndex > 0)
                {
                    skip = ((pageIndex - 1) * pageSize) + 1;
                }
                where = where.Length > 0 ? where + " AND SCBJ=0 AND ZHLX<>'sys'" : " SCBJ=0 AND ZHLX<>'sys'";
                string sql = string.Format($@"SELECT * FROM
                            (SELECT ROW_NUMBER() OVER(ORDER BY {sort}) AS RowNum,*,'删除' as btn,'禁止' as JZDL,'恢复' as HFDL  FROM  [dbo].[QH_YHXX] WHERE {where} and isnull(DLZH,'')<>'') AS result
                            WHERE  RowNum >= {skip}   AND RowNum <= {take} ORDER BY {sort}");
                string sql_count = $"SELECT COUNT(1) FROM [dbo].[QH_YHXX] with (nolock) where {where}";
                using (DBHelper help = new DBHelper())
                {
                    result.Data = help.DbContext.Query<QH_YHXX>(sql) as List<QH_YHXX>;
                    result.Count = (int)help.DbContext.ExecuteScalar(sql_count);
                }
            }
            catch (Exception ex)
            {
                result.Code = ResultCode.Fail;
                result.Message = ex.Message;
            }
            return result;
        }

        /// <summary>
        /// 根据条件统计条目
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public static int GetCount(string where)
        {
            try
            {
                where = where.Length > 0 ? where + " AND SCBJ=0" : " SCBJ=0";
                string sql = string.Format(@"SELECT count(1) count FROM [dbo].[QH_YHXX] with (nolock) WHERE {0}", where);
                using (DBHelper help = new DBHelper())
                {
                    return (int)help.DbContext.ExecuteScalar(sql);
                }
            }
            catch (Exception ex)
            {
                return 0;
            }
            return 0;
        }

        /// <summary>
        /// 编辑 修改用户状态
        /// </summary>
        /// <param name="id">用户id</param>
        /// <param name="yhzt">用户状态 正常，禁止</param>
        /// <returns></returns>
        public static ResultModel<int> EditDLZT(string id, string yhzt)
        {
            ResultModel<int> result = new ResultModel<int>();
            try
            {
                string sql = string.Format(@"UPDATE [dbo].[QH_YHXX] SET DLZT='{0}' WHERE ID = '{1}'", yhzt, id);
                using (DBHelper help = new DBHelper())
                {
                    //修改用户
                    result.Count = help.DbContext.Execute(sql);
                    if (result.Count == 0)
                    {
                        result.Code = ResultCode.Fail;
                        result.Message = "保存失败";
                    }
                }
            }
            catch (Exception ex)
            {
                result.Code = ResultCode.Fail;
                result.Message = ex.Message;
            }
            return result;
        }

        /// <summary>
        /// 编辑 修改账户类型
        /// </summary>
        /// <param name="id">用户id</param>
        /// <param name="zhlx">账户类型</param>
        /// <returns></returns>
        public static ResultModel<int> EditZHLX(string id, string zhlx)
        {
            ResultModel<int> result = new ResultModel<int>();
            try
            {
                string sql = string.Format(@"UPDATE [dbo].[QH_YHXX] SET ZHLX='{0}' WHERE ID = '{1}'", zhlx, id);
                using (DBHelper help = new DBHelper())
                {
                    //修改用户
                    result.Count = help.DbContext.Execute(sql);
                    if (result.Count == 0)
                    {
                        result.Code = ResultCode.Fail;
                        result.Message = "设置账号权限失败，请联系管理员";
                    }
                    else
                    {
                        result.Message = "已设置该账号为：" + zhlx;
                    }
                }
            }
            catch (Exception ex)
            {
                result.Code = ResultCode.Fail;
                result.Message = ex.Message;
            }
            return result;
        }

        /// <summary>
        /// 初始化密码
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static ResultModel<int> ResetPWD(string ID)
        {
            ResultModel<int> res = new ResultModel<int>();
            try
            {
                string sql = string.Format(@"UPDATE QH_YHXX
                                                SET DLMM = '{0}' WHERE ID='{1}'", SetSailCommon.CommonHelper.MD5Encrypt("12345678"), ID);
                using (DBHelper help = new DBHelper())
                {
                    res.Count = help.DbContext.Execute(sql);
                    if (res.Count == 0)
                    {
                        res.Code = ResultCode.Fail;
                        res.Message = "密码初始化失败";
                    }
                    else
                    {
                        res.Message = "密码初始化成功，默认密码：12345678";
                    }
                }
            }
            catch (Exception ex)
            {
                res.Code = ResultCode.Exception;
                res.Message = ex.Message;
            }
            return res;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static ResultModel<int> EditZDGL(string ID)
        {
            ResultModel<int> result = new ResultModel<int>();
            try
            {
                string sql = string.Format(@"UPDATE [dbo].[QH_YHXX] SET SCBJ=1 WHERE ID = '{0}'", ID);
                using (DBHelper help = new DBHelper())
                {
                    result.Count = help.DbContext.Execute(sql);
                    if (result.Count == 0)
                    {
                        result.Code = ResultCode.Fail;
                        result.Message = "删除失败";
                    }
                }
            }
            catch (Exception ex)
            {
                result.Code = ResultCode.Fail;
                result.Message = ex.Message;
            }
            return result;
        }
    }
}
